"""
Case Type   : security-EqualityQuery
Case Name   : 全密态列加密,通过函数将加密内容赋值给变量，查看变量的返回值
Create At   : 2023/10/11
Owner       : opentestcase003
Description :
    1.创建cmk&cek
    2.创建表给列加密,插入数据
    3.创建函数
    4.调用函数
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
    Modified by peilinqian at 2025-1-22:优化用例配置$GAUSSHOME/etc/localkms路径步骤，排除app路径拼接错误问题
    2024/11/22 modify by @peilinqian: 定位偶现问题单IATFYV，增加调试打印参数
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Auditing(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.userNode = Node('PrimaryDbUser')
        self.database = self.userNode.db_name
        self.com = Common()
        self.cmk1 = 'cmk_security_qualityquery_0060_1'
        self.cmk2 = 'cmk_security_qualityquery_0060_2'
        self.cek1 = 'cek_security_qualityquery_0060_1'
        self.cek2 = 'cek_security_qualityquery_0060_2'
        self.key_path1 = 'keypath_security_qualityquery_0060_1'
        self.key_path2 = 'keypath_security_qualityquery_0060_2'
        self.table = 'table_security_qualityquery_0060'
        self.function = 'function_security_qualityquery_0060'

    def test_security(self):
        text = '-----step1:创建cmk&cek;expect:成功-----'
        self.logger.info(text)
        text = '-----配置$GAUSSHOME/etc/localkms路径;expect:成功-----'
        self.logger.info(text)
        mkdir_cmd = f'source {macro.DB_ENV_PATH};' \
                    f'if [ ! -d "$GAUSSHOME/etc/localkms" ];' \
                    f'then mkdir $GAUSSHOME/etc/localkms;fi'
        self.logger.info(mkdir_cmd)
        mkdir_msg = self.userNode.sh(mkdir_cmd).result()
        self.assertTrue(mkdir_msg == '' or 'File exists' in mkdir_msg)

        text = '-----step2.1:CMK、CEK;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'drop client master key if exists {self.cmk1};' \
            f'drop client master key if exists {self.cmk2};' \
            f'drop column encryption key if exists {self.cek1};' \
            f'drop column encryption key if exists {self.cek2};' \
            f'create client master key {self.cmk1} with ' \
            f'(key_store = localkms, key_path = "{self.key_path1}", ' \
            f'algorithm = "rsa_3072");create client master key ' \
            f'{self.cmk2} with (key_store = localkms, key_path =' \
            f' "{self.key_path2}", algorithm = "rsa_3072");' \
            f'create column encryption key {self.cek1} with values ' \
            f'(client_master_key = {self.cmk1}, algorithm = ' \
            f'aead_aes_256_cbc_hmac_sha256);create column encryption key ' \
            f'{self.cek2} with values (client_master_key = {self.cmk2}, ' \
            f'algorithm = aead_aes_256_cbc_hmac_sha256);'
        self.logger.info(sql_cmd)
        msg = self.sh_primy.execut_db_sql(sql_cmd, sql_type='-3 -C')
        self.logger.info(msg)
        self.assertNotIn('ERROR', msg.upper(), text + '执行失败')
        self.assertIn(self.constant.create_cek_success, msg, text +
                      '执行失败')

        text = '-----step2.2:创建表给列加密;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'drop table if exists {self.table};' \
            f'create table {self.table} (id_number int, name text ' \
            f'encrypted with (column_encryption_key = {self.cek1}, ' \
            f'encryption_type = deterministic),credit_card varchar(19) ' \
            f'encrypted with (column_encryption_key = {self.cek2}, ' \
            f'encryption_type = deterministic)) with (orientation=row);'
        self.logger.info(sql_cmd)
        result = self.sh_primy.execut_db_sql(sql_cmd, sql_type='-3 -C')
        self.logger.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result, text +
                      '执行失败')
        
        text = '-----step2.3:插入数据;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'insert into {self.table} ' \
            f'values(1,\'avi\',\'1234567890123456\');' \
            f'insert into {self.table} values(2,\'eli\',\'2345678901234567\');'
        self.logger.info(sql_cmd)
        result = self.sh_primy.execut_db_sql(sql_cmd, sql_type='-3 -C')
        self.logger.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result, text +
                      '执行失败')

        text = '-----step3:创建函数;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'drop function if exists {self.function};' \
            f'create function {self.function}(val text, val2 varchar(9)) ' \
            f'returns text as \$\$ declare c text;begin select into c name ' \
            f'from {self.table} where name=\$1 or credit_card=\$2 limit 1;' \
            f'return c;end;\$\$ language plpgsql;'
        self.logger.info(sql_cmd)
        msg = self.sh_primy.execut_db_sql(sql_cmd, sql_type='-3 -C')
        self.logger.info(msg)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, msg,
                      text + '执行失败')

        text = '-----step4:调用函数;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'select {self.function}(\'avi\', val2=>\'12345678\');'
        self.logger.info(sql_cmd)
        msg = self.sh_primy.execut_db_sql(sql_cmd, sql_type='-3 -C')
        self.logger.info(msg)
        self.assertIn('\\x', msg, text + '执行失败')
        self.assertNotIn('avi', msg, text + '执行失败')

    def tearDown(self):
        text = '-----step4:清理环境;expect:成功-----'
        self.logger.info(text)
        sql_cmd = f'drop function {self.function};' \
            f'drop table {self.table};' \
            f'drop column encryption key if exists {self.cek1};' \
            f'drop column encryption key if exists {self.cek2};' \
            f'drop client master key if exists {self.cmk1};' \
            f'drop client master key if exists {self.cmk2};'
        self.logger.info(sql_cmd)
        msg = self.sh_primy.execut_db_sql(sql_cmd, sql_type='-3 -C')
        self.logger.info(msg)
        self.assertIn(self.constant.drop_cmk_success, msg, text +
                      '执行失败')
        self.assertNotIn('ERROR', msg.upper(), text + '执行失败')
        clean_cmd = f"source {macro.DB_ENV_PATH};" \
                    f"rm -rf $GAUSSHOME/etc/localkms;" \
                    f"ls $GAUSSHOME/etc/localkms"
        self.logger.info(clean_cmd)
        clean_msg = self.userNode.sh(clean_cmd).result()
        self.logger.info(clean_msg)
        self.assertIn(f"No such file or directory", clean_msg,
                      text + '执行失败')
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
        